package cn.com.dhcc.turbo.dao.registry;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.CM;
import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.handler.modulehandler.WhereCondition;
import cn.com.dhcc.app.core.service.Log;
import cn.com.dhcc.app.core.service.Log.LOGTYPE;
import cn.com.dhcc.app.pub.core.consts.NodeInfoParam;
import cn.com.dhcc.turbo.entity.registry.NodePermission;
import cn.com.dhcc.turbo.entity.registry.OrgInfo;
import cn.com.dhcc.turbo.entity.registry.wapper.NodePermissionRowWapper;
import cn.com.dhcc.turbo.entity.registry.wapper.OrgInfoRowWapper;
/**
 * 行政区信息表dao
 * 
 * @日期：2014-5-12 下午3:05:03
 * @作者：张洪涛
 * @版权所有：东华软件
 * @版本：1.0
 */
@Repository
public class OrgInfoDao extends BaseDao {
	@SuppressWarnings("deprecation")
	private static Logger logger = Log.getExistLogger(LOGTYPE.SAI);

	/**
	 * 获取省级、流域行政区列表
	 * @return
	 * @throws DaoException
	 */
	public List<OrgInfo> getProv() throws DaoException {
		String msg = "";
		String sql = "select org_code,"
       +" org_name,"
       +" case"
       +"  when org_code like '0%' then '2'"
       +"  when substr(org_code, 1, 2) in ('11', '12', '31', '50') then"
       +"    '0'"
       +"   else   '1'"
       +" end org_type"
       +" from "+ SI_TABLE("turbo_org_info") 
       +" where org_code like '__0000' order by org_type,org_code";
		try {
			return CM.getDao().list("获取行政区列表", sql,
					new OrgInfoRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取节点信息列表失败";
			logger.error("获取节点信息列表失败", e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 获取二级行政区
	 * @param orgCode　行政区编码
	 * @return
	 * @throws DaoException
	 */
	public List<OrgInfo> getCityByOrgCode(String orgCode) throws DaoException {
		String msg = "";
		String sql="select  org_code, org_name"
				   +" from "+ SI_TABLE("turbo_org_info") 
				   +" where org_code like '"+orgCode.substring(0,2)+"__00' order by org_code";
		try {
			return CM.getDao().list("获取二级行政区列表", sql,
					new OrgInfoRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取节点信息列表失败";
			logger.error("获取节点信息列表失败", e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 获取二级行政区
	 * @param prov　一级行政区编码
	 * @return
	 * @throws DaoException
	 */
	public List<Map<String, Object>> getCity(String prov) throws DaoException {
		String msg = "";
		String sql="select  org_code, org_name"
						   +" from "+ SI_TABLE("turbo_org_info") 
						   +" where org_code like '"+prov.substring(0,2)+"__00' order by org_code";
		try {
			return CM.getDao().list("获取二级行政区列表", sql, new RowMapper<Map<String, Object>>() {
				@Override
				public Map<String, Object> mapRow(ResultSet rs, int rownum) throws SQLException {
					Map<String, Object> st = new HashMap<String, Object>();
					st.put("code", rs.getString("org_code"));
					st.put("name", rs.getString("org_name"));
					return st;
				}
			},	new Object[] {});
		} catch (DaoException e) {
			msg = "获取节点信息列表失败";
			logger.error("获取节点信息列表失败", e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 获取三级行政区
	 * @param orgCode 行政区编码
	 * @return
	 * @throws DaoException
	 */
	public List<OrgInfo> getTownByOrgCode(String orgCode) throws DaoException {
		String msg = "";
		String sql="select  org_code, org_name"
				   +" from "+ SI_TABLE("turbo_org_info") 
				   +" where org_code like '"+orgCode.substring(0,4)+"__' or (substr(org_code, 1, 2) in ('11', '12', '31', '50') and org_code like '"+orgCode.substring(0,2)+"____') order by org_code";
		try {
			return CM.getDao().list("获取三级行政区列表", sql,
					new OrgInfoRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取三级行政区失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取三级行政区
	 * @param city 二级行政区编码
	 * @return
	 * @throws DaoException
	 */
	public List<Map<String, Object>> getTown(String city) throws DaoException {
		String msg = "";
		String sql="select  org_code, org_name"
						   +" from "+ SI_TABLE("turbo_org_info") 
						   +" where org_code like '"+city.substring(0,4)+"__' or (substr(org_code, 1, 2) in ('11', '12', '31', '50') and org_code like '"+city.substring(0,2)+"____') order by org_code";
		try {
			return CM.getDao().list("获取三级行政区列表", sql, new RowMapper<Map<String, Object>>() {
				@Override
				public Map<String, Object> mapRow(ResultSet rs, int rownum) throws SQLException {
					Map<String, Object> st = new HashMap<String, Object>();
					st.put("code", rs.getString("org_code"));
					st.put("name", rs.getString("org_name"));
					return st;
				}
			},	new Object[] {});
		} catch (DaoException e) {
			msg = "获取三级行政区列表失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 分析所选节点类型，获得所选节点的下级所有行政区
	 * @param code
	 * @return
	 * @throws DaoException
	 */
	public List<OrgInfo> getListByNodeCode(String code) throws DaoException {
		String whereSQL = "";
		if(NodeInfoParam.MWR_NODEREGION_CODE.equals(code)){//水利部
			whereSQL = "t.ad_cd like '__0000' ";
		}else if(code.equals("110000") || code.equals("120000") || code.equals("310000") || code.equals("500000")){
			whereSQL = "t.ad_cd like '"+code.substring(0,2)+"____' ";
		} else if("0000".equals(code.substring(2,6))){//省，自治区、流域
			whereSQL = "t.ad_cd like '"+code.substring(0,2)+"__00' ";
		}else if("00".equals(code.substring(4,6))){//市
			whereSQL = "t.ad_cd like '"+code.substring(0,4)+"__' ";
		}
		String sql = "select ad_cd,ad_nm from "+ SI_TABLE("turbo_node_region") +" t  where "
							+whereSQL
							+ " and t.ad_cd <>? order by t.ad_cd";
		try {
			return CM.getDao().list("获取行政区列表", sql, new OrgInfoRowWapper(),	new Object[] {code});
		} catch (DaoException e) {
			logger.error("获取行政区列表失败", e);
			throw new DaoException("获取行政区列表失败", e);
		}
	}
	/**
	 * 获取行政区
	 * @param code
	 * @return
	 * @throws DaoException
	 */
	public OrgInfo get(String code)throws DaoException {
		String sql = "select * from "+ SI_TABLE("turbo_node_region") +" t  where "
				+ " t.ad_cd =? ";
		try {
			return CM.getDao().get("获取行政区", sql, new OrgInfoRowWapper(),	new Object[] {code});
		} catch (DaoException e) {
			logger.error("获取行政区失败", e);
			throw new DaoException("获取行政区失败", e);
		}
	}
	/**
	 *获得管理权限内所有节点
	 * @return
	 * @throws DaoException
	 */
	public List<OrgInfo> getListByRegionCode() throws DaoException {
		String regionCode =  NodeInfoParam.NODE_REGION_CODE;
		String sql = "select * from "+ SI_TABLE("turbo_node_region")
							+" where ad_cd in(" 
							+"						select regexp_substr(ad_cd_to_mgr,'[^,]+',1,rn) ad_cd_to_mgr"
							+ " 						from "+ SI_TABLE("turbo_node_permission")+",(select rownum rn from dual connect by rownum<=230)"
							+ " 						where regexp_substr(ad_cd_to_mgr,'[^,]+',1,rn) is not null and  ad_cd = ?"
							+ ") order by ad_cd";
		try {
			return CM.getDao().list("获取行政区列表", sql, new OrgInfoRowWapper(),
					new Object[] { regionCode });
		} catch (DaoException e) {
			logger.error("获取行政区列表失败", e);
			throw new DaoException("获取行政区列表失败", e);
		}
	}
	/**
	 * 获取行政区管理权限
	 * @param code
	 * @return
	 * @throws DaoException
	 */
	public NodePermission getPermission(String code)throws DaoException {
		String sql = "select * from "+ SI_TABLE("turbo_node_permission") +" t  where  t.ad_cd =? ";
		try {
			return CM.getDao().get("获取行政区管理权限", sql, new NodePermissionRowWapper(),	new Object[] {code});
		} catch (DaoException e) {
			logger.error("获取行政区管理权限失败", e);
			throw new DaoException("获取行政区失败", e);
		}
	}
	public List<OrgInfo> getListByNetType(String netType) throws DaoException {
		String regionCode =  NodeInfoParam.NODE_REGION_CODE;
		String sql = "select ad_cd,"
					      +" case"
					      +"   when ad_cd = subStr(ad_cd, 0, 2) || '0000' then"
					      +"     ad_nm"
					      +"    when ad_cd = subStr(ad_cd, 0, 4) || '00' then"
					      +"     '  ' || ad_nm"
					      +"     else"
					      +"     '    ' || ad_nm"
					      +"   end ad_nm,slszy_node,nt, ts from "+ SI_TABLE("turbo_node_region")
							+" where ad_cd in(" 
							+"	 select regexp_substr(ad_cd_to_mgr,'[^,]+',1,rn) ad_cd_to_mgr"
							+ "  from "+ SI_TABLE("turbo_node_permission")+",(select rownum rn from dual connect by rownum<=230)"
							+ "  where regexp_substr(ad_cd_to_mgr,'[^,]+',1,rn) is not null and  ad_cd = ?) " 
//							+ " and ad_cd not in (select subStr(node_code,0,6) from turbo_node_info where  status='1' and net_type=? and subStr(node_code,0,6) <>?)  "
							+" order by ad_cd";
		try {
			return CM.getDao().list("获取行政区列表", sql, new OrgInfoRowWapper(),
//					new Object[] { regionCode,netType,regionCode });
					new Object[] { regionCode});
		} catch (DaoException e) {
			logger.error("获取行政区列表失败", e);
			throw new DaoException("获取行政区列表失败", e);
		}
	}
	
	public String getParentCode(String orgCode) throws DaoException {
		try {
			WhereCondition condition = new WhereCondition();
			condition.where().notEq("ad_cd", orgCode).and().like("ad_cd_to_mgr", "%"+orgCode+"%");
			String sql = "select ad_cd from "+ SI_TABLE("turbo_node_permission");
			return CM.getDao().queryForString("通过节点编码获得节点", sql, condition,new Object[] {});
		} catch (DaoException e) {
			throw new DaoException("通过节点编码获得节点失败！", e);
		}
	}
	
}
